#include<mysql/mysql.h>
#include<stdarg.h>
#include<stdio.h>
#include<string.h>
#include<stdlib.h>
#include<time.h>
#define JP_MAXSQL_LEN 2048
#define JP_ROW MYSQL_ROW
#define JP_CON mysql_conn
#define JP_RES mysql_res
#define JP_EXE mysql_fetch_row
MYSQL *mysql_conn=NULL;
MYSQL_RES *mysql_res=NULL;
/*****************************
mysql config
*****************************/
const char *jp_mysql_host="localhost";
const char *jp_mysql_user="root";
const char *jp_mysql_passwd="wjp2014";
const char *jp_mysql_dbname="network";
const unsigned int jp_mysql_port=3306;
/****************************
mysql init 
****************************/
int jp_init_mysql(){
	JP_CON=mysql_init(NULL);
	if(!mysql_real_connect(JP_CON,jp_mysql_host,jp_mysql_user,jp_mysql_passwd,jp_mysql_dbname,jp_mysql_port,NULL,0)){
	//printf("fail!\n");
	return 0;
}
	mysql_query(JP_CON,"set names UTF-8");
	return 1;
}
/*******************************
mysql close
*******************************/
void jp_close_mysql(){
	mysql_free_result(JP_RES);
	mysql_close(JP_CON);
}
/********************************
mysql query
arg:sql string
********************************/
int jp_query(char *sql){
	int res=mysql_query(JP_CON,sql);
	return res;
}
/*************************************
mysql insert
arg1:table name
arg...:field=value
return:sql string
notic:must be end "jend"
demo:jp_insert("mac_user","mac='dd:22:44:22'","bind_user='zqd'","jend");
*************************************/
char *jp_insert(char *table,...){
	va_list args;
	char *sql=malloc(JP_MAXSQL_LEN);
	char *values=malloc(JP_MAXSQL_LEN);
	memset(sql,0,JP_MAXSQL_LEN);
	memset(values,0,JP_MAXSQL_LEN);
	sql=strcat(sql,"insert into ");
	sql=strcat(sql,table);
	sql=strcat(sql,"(");
	values=strcat(values," values(");
	va_start(args,table);
	char *cond=NULL;
	while((cond=va_arg(args,char *))!="jend"){
		int clen=strlen(cond)+1;
		char *clone=malloc(clen);
		memcpy(clone,cond,clen);
		char *var=strtok(clone,"=");
		sql=strcat(sql,var);
		sql=strcat(sql,",");
		var=strtok(NULL,"=");
		values=strcat(values,var);
		values=strcat(values,",");
		free(clone);	
	}
	va_end(args);
	int ti=strlen(sql)-1;
	int tj=strlen(values)-1;
	sql[ti]='\0';
	values[tj]='\0';
	sql=strcat(sql,")");
	values=strcat(values,")");
	sql=strcat(sql,values);
	free(values);
	char *rel_sql=malloc(strlen(sql)+1);
	memcpy(rel_sql,sql,strlen(sql)+1);
	free(sql);
	jp_query(rel_sql);
	return rel_sql;
}
/**********************************************************
mysql update
arg1:table name
arg2:where cond
arg...:field=value fields
return:sql string
notic:must be end of "jend"
demo:
jp_update("mac_user","id=1","mac='ff:22:55:14:11'","bind_user='zqd'","jend")
**********************************************************/
char *jp_update(char *table,char *where,...){
	va_list args;
	char *sql=malloc(JP_MAXSQL_LEN);
	memset(sql,0,JP_MAXSQL_LEN);
	sql=strcat(sql,"update ");
	sql=strcat(sql,table);
	sql=strcat(sql," set ");
	va_start(args,where);
	char *cond=NULL;
	while((cond=va_arg(args,char *))!="jend"){
		sql=strcat(sql,cond);
		sql=strcat(sql,",");
	}
	int ti=strlen(sql)-1;
	sql[ti]='\0';
	if(where!=NULL){
		sql=strcat(sql," where ");
		sql=strcat(sql,where);
	}
	va_end(args);
	char *rel_sql=malloc(strlen(sql)+1);
	memcpy(rel_sql,sql,strlen(sql)+1);
	free(sql);
	jp_query(rel_sql);
	return rel_sql;

}
/************************************************
mysql select
arg1:table name
arg2:where cond
arg...:fields,to show fileds;
notic:must be end of "jend"

************************************************/
char *jp_select(char *table,char *where,...){
	va_list args;
	va_start(args,where);
	char *field=NULL;
	char *sql=malloc(JP_MAXSQL_LEN);
	memset(sql,0,JP_MAXSQL_LEN);
	sql=strcat(sql,"select ");
	while((field=va_arg(args,char *))!="jend"){
		sql=strcat(sql,field);
		sql=strcat(sql,",");
	}
	int ti=strlen(sql)-1;
	sql[ti]='\0';
	va_end(args);
	sql=strcat(sql," from ");
	sql=strcat(sql,table);
	if(where!=NULL){
		sql=strcat(sql," where ");
		sql=strcat(sql,where);
	}
	char *rel_sql=malloc(strlen(sql)+1);
	memcpy(rel_sql,sql,strlen(sql)+1);
	jp_query(rel_sql);
	JP_RES=mysql_store_result(JP_CON);
	return rel_sql;
}
/**************************************
判断MAC是否存在，不存在则插入一条数据
arg:MAC物理地址
**************************************/
void jp_add_mac(char *mac){
	if(mac!=NULL){
		char *where=malloc(JP_MAXSQL_LEN);
		memset(where,0,JP_MAXSQL_LEN);
		jp_init_mysql();
		where=strcat(where,"mac='");
		where=strcat(where,mac);
		where=strcat(where,"'");
		jp_select("mac_user",where,"count(*)","jend");
		JP_ROW row=JP_EXE(JP_RES);
		time_t t;
		t=time(0);
		if(strcmp(row[0],"0")==0){
			char tmac[255];
			char create_time[255];
			sprintf(tmac,"mac='%s'",mac);
			sprintf(create_time,"create_time=%ld",(long)t);
			//char *sql=NULL;
			
			jp_insert("mac_user",tmac,create_time,"jend");
			//printf("mac:%s,add success!\n",mac);
			//printf("%s\n",sql);
		}else{
			//printf("mac:%s,is exist\n",mac);
		}
		jp_close_mysql();
		free(where);
	}
}
/********************************************
更新mac最后一次连接所使用的协议
arg1:MAC物理地址
arg2:所使用的协议
********************************************/
void jp_update_protocol(char *mac,char *prol){
	if(mac!=NULL){
		char *where=malloc(JP_MAXSQL_LEN);
                memset(where,0,JP_MAXSQL_LEN);
                jp_init_mysql();
                where=strcat(where,"mac='");
                where=strcat(where,mac);
                where=strcat(where,"'");
                jp_select("mac_user",where,"count(*)","jend");
		JP_ROW row=JP_EXE(JP_RES);
		if(strcmp(row[0],"0")!=0){
			char jprol[255];
			
                        sprintf(jprol,"protocol='%s'",prol);

			jp_update("mac_user",where,jprol,"jend");
			//printf("update protocol:%s success!\n",prol);
			
		}

		free(where);

	}
}
/************************************
更新mac所绑定的ip地址
arg1:MAC地址
arg2:ip地址
************************************/
void jp_update_ip(char *mac,char *ip){
	if(mac!=NULL){
		char *where=malloc(JP_MAXSQL_LEN);
		memset(where,0,JP_MAXSQL_LEN);
		sprintf(where,"mac='%s' and bind_ip!='%s'",mac,ip);
		jp_init_mysql();
		jp_select("mac_user",where,"count(*)","jend");
		JP_ROW row=JP_EXE(JP_RES);
		if(strcmp(row[0],"0")==0){
			char jip[255];
			sprintf(where,"mac='%s'",mac);
			sprintf(jip,"bind_ip='%s'",ip);
			jp_update("mac_user",where,jip,"jend");
		}
		jp_close_mysql();
		free(where);
	}
}
